/**
 * $Id: OrganizationDao.java 6 2011-09-14 00:18:07Z adriftrock@gmail.com $
 */

package com.swehr.server.daos.impl;

import com.google.inject.Inject;
import com.hp.hpl.jena.query.*;
import com.hp.hpl.jena.rdf.model.Literal;
import com.hp.hpl.jena.rdf.model.Resource;
import com.swehr.server.daos.IOrganizationDao;
import com.swehr.server.models.Organization;
import com.swehr.utils.DateUtil;
import com.swehr.utils.StringUtil;
import org.apache.log4j.Logger;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

/**
 * This is a data access object for Organization Management.
 *
 * @author Rock Huang
 * @version 0.1
 */
public class OrganizationDao implements IOrganizationDao{

    private static Logger logger = Logger.getLogger(Organization.class);
    private InferenceDao inf;

    @Inject
    public void setInf(InferenceDao inf) {
        this.inf = inf;
    }

    /**
     * Save triples of an Organization.
     *
     * @param org The Organization that needs to save.
     */
    public void save(Organization org) {
        StringBuffer cmd = new StringBuffer(1024);
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("INSERT DATA {\n");
        cmd.append("  GRAPH ").append(Const.GRAPH_ORG).append(" { \n");
        cmd.append("    " + Const.URI_ORG + " rdf:type ehr:Organization ; \n");
        cmd.append("    ehr:hasId @id ; \n");
        cmd.append("    ehr:hasName \"@nm\" ; \n");
        cmd.append("    ehr:hasEmail \"@em\" ; \n");
        if (org.getWebsite() != null) {
            cmd.append("    ehr:website \"").append(org.getWebsite()).append("\" ; \n");
        }
        if (org.getEmployer() != null) {
            cmd.append("    ehr:hasEmployer @er ; \n");
        }
        if (org.getPassword() != null) {
            cmd.append("    ehr:password \"@psw\" ; \n");
        }
        if (org.getPhoneNumber() != null) {
            cmd.append("    ehr:hasPhoneNumber  \"@pn\" \n");
        } else {
            SparqlBuilder.replaceLast(cmd, ";", "");
        }
        cmd.append("  }\n}");


        // replacing the parameters
        SparqlBuilder.replace(cmd, "@nm", org.getName());
        SparqlBuilder.replace(cmd, "@em", org.getEmail());

        if (org.getEmployer() != null) {
            SparqlBuilder.replace(cmd, "@er", org.getEmployer());
        }
        if (org.getPhoneNumber() != null) {
            SparqlBuilder.replace(cmd, "@pn", org.getPhoneNumber());
        }
        if (org.getPassword() != null) {
            SparqlBuilder.replace(cmd, "@psw", org.getPassword());
        }

        try {
            // generating a sequence for the organization
            int id = IdGenerator.getInstance().getMaxOrgId();
            SparqlBuilder.replace(cmd, "@id", String.valueOf(++id));
            SparqlBuilder.replace(cmd, "@id", String.valueOf(id));

            // the id will return to client and indicate the Organization is
            // successfully saved or not. If id == 0 that means unsuccessful.
            org.setId(id);

            // increasing ID cache
            IdGenerator.getInstance().setMaxOrgId(id);

            boolean isSuccess = conn.execUpdate(cmd.toString(), inf);
            if (!isSuccess) org.setId(0);
        } catch (Exception e) {
            logger.error(e);
            e.printStackTrace();
            org.setId(0);
        }
    }

    public void update(Organization org) {
        StringBuffer cmd = new StringBuffer(1024);
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("WITH ").append(Const.GRAPH_ORG + "\n");
        cmd.append("DELETE { @nm @em @pn } \n");
        cmd.append("INSERT { @nm @em @pn } \n");
        cmd.append("WHERE {\n");
        cmd.append("  ?o a ehr:Organization .\n");
        cmd.append("  ?o ehr:hasId @id \n}");

        // check if the given organization object has no id value then return.
        if (org.getId() == 0) {
            throw new NullPointerException("Cannot found Organization ID");
        } else {
            int p = cmd.indexOf("@id");
            cmd.replace(p, p+3, String.valueOf(org.getId()));
        }

        // get old one from DB by org ID.
        Organization old = findById(org.getId());

        // compare with old one and prepare partial SPARQL
        boolean seg = false;
        seg = SparqlBuilder.replace(cmd, "@nm", "hasName", old.getName(),
                org.getName(), seg,
                SparqlBuilder.isModified(org.getName(), old.getName()));
        seg = SparqlBuilder.replace(cmd, "@em", "hasEmail", old.getEmail(),
                org.getEmail(), seg,
                SparqlBuilder.isModified(org.getEmail(), old.getEmail()));
        SparqlBuilder.replace(cmd, "@pn", "hasPhoneNumber", old.getPhoneNumber(),
                org.getPhoneNumber(), seg,
                SparqlBuilder.isModified(org.getPhoneNumber(), old.getPhoneNumber()));

        boolean isSuccess = conn.execUpdate(cmd.toString(), inf);
        if (!isSuccess) org.setId(0);
    }

    public String queryOrgFromDBPedia(String website) {
        String uri1 = SparqlBuilder.wrapUriPrefix(website);
        String uri2 = SparqlBuilder.changeSuffix(uri1);
        StringBuffer cmd = new StringBuffer(512);
        ConnectDao conn = new ConnectDao();
        conn.setLinkedNS(cmd);
        cmd.append("SELECT DISTINCT ?org WHERE { ");
        cmd.append("{ ?org foaf:homepage <").append(uri1).append("> } ");
        cmd.append("UNION { ?org foaf:homepage <").append(uri2).append("> } }");
        logger.debug("\n### Find Org info from DBPedia by website ###\n");
        logger.debug("\n" + cmd.toString());
        Query q = QueryFactory.create(cmd.toString());
        QueryExecution qe = QueryExecutionFactory.sparqlService(Const.DBPEDIA, q);
        ResultSet rs = qe.execSelect();
        String resource = null;
        if (rs.hasNext()) {
            resource = rs.nextSolution().getResource("?org").getURI();
        }
        qe.close();
        return resource;
    }

    public void linkOrgResource(String orgId, String website) {
        String dbpOrgRes = queryOrgFromDBPedia(website);
        if (dbpOrgRes == null) return;
        String localOrgRes = SparqlBuilder.wrapOrg(orgId);
        StringBuffer cmd = new StringBuffer(128);
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("INSERT DATA { ").append(localOrgRes).append(" owl:sameAs ");
        cmd.append("<").append(dbpOrgRes).append("> }");
        logger.debug("\n### Linking same Org resources ###\n");
        conn.execUpdate(cmd.toString(), inf);
    }

    public List<Organization> find() {
        String s = "?parent a ehr:Organization . \n  " +
                "?ot rdfs:subClassOf ehr:Organization . \n  " +
                "?o rdf:type ?ot . ?o ehr:isOrgOf ?parent . \n  " +
                "?o ehr:hasId ?id . ?o ehr:hasName ?nm .\n  ";
        return find(s);
    }

    public List<Organization> findSubOrg(String parentId) {
        StringBuffer s = new StringBuffer(384);
        s.append("?o ehr:isOrgOf ").append(Const.URI_ORG).append(" . \n  ");
        s.append("?ot rdfs:subClassOf ehr:Organization . \n  ");
        s.append("?o rdf:type ?ot . \n  ");
        s.append("?o ehr:hasId ?id . ?o ehr:hasName ?nm .\n  ");
        SparqlBuilder.replace(s, "@id", parentId);
        return find(s.toString());
    }

    public List<Organization> find(String cond) {
        StringBuffer cmd = new StringBuffer(512);
        cmd.append("SELECT distinct * WHERE { \n  ");
        cmd.append(cond);
        cmd.append("OPTIONAL { ?o ehr:hasEmail ?em } .\n  ");
        cmd.append("OPTIONAL { ?o ehr:hasPhoneNumber ?pn } .\n  ");
        cmd.append("OPTIONAL { ?o ehr:website ?ws} . \n  ");
        cmd.append("OPTIONAL { ?o ehr:isDepartmentOf ?dp FILTER regex(str(?dp), \"organizations\") } .\n  ");
        cmd.append("OPTIONAL { ?o ehr:isSubsidiaryOf ?sb FILTER regex(str(?sb), \"organizations\") } .\n}");

        List<Organization> list = new ArrayList<Organization>();
        QueryExecution qe = null;
        try {
            qe = inf.create(cmd.toString());
            logger.debug("### Find Org on inference model ###");
            ResultSet rs = qe.execSelect();
            while (rs.hasNext()) {
                list.add(one(rs.nextSolution()));
            }
        } catch (Exception e) {
            logger.error("find(), failed operation.", e);
        } finally {
            if (qe != null) {
                qe.close();
            }
        }

        return list;
    }

    public Organization one(QuerySolution qs) throws ParseException {
        Organization org = new Organization();
        org.setId(qs.getLiteral("?id").getInt());
        org.setName(qs.getLiteral("?nm").getString());
        Literal em = qs.getLiteral("?em");
        org.setEmail(em != null ? em.getString() : "");
        Literal pn = qs.getLiteral("?pn");
        org.setPhoneNumber(pn != null ? pn.getString() : "");
        Resource ot = qs.getResource("?ot");
        org.setOrgType(ot != null ? ot.getLocalName() : "");
        Resource dp = qs.getResource("?dp");
        Resource sb = qs.getResource("?sb");
        if (dp != null) org.setParent(dp.getURI());
        else if (sb != null) org.setParent(sb.getURI());
        else org.setParent("");
        Resource er = qs.getResource("?er");
        org.setEmployer(er != null ? er.getURI() : "");
        Literal ws = qs.getLiteral("?ws");
        org.setWebsite(ws != null ? ws.getString() : "");
        return org;
    }

    public Organization findById(int id) {
        StringBuffer cmd = new StringBuffer(1024);
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("SELECT * \n");
        cmd.append("FROM ").append(Const.GRAPH_ORG).append("\n");
        cmd.append("WHERE{ \n");
        cmd.append("   GRAPH ").append(Const.GRAPH_ORG).append(" {\n");
        cmd.append("    " + Const.URI_ORG + " ehr:hasId ?id ; \n");
        cmd.append("    ehr:hasName ?nm ; \n");
        cmd.append("    a ?ot . \n");
        cmd.append("    OPTIONAL { ").append(Const.URI_ORG).append(" ehr:website ?ws } . \n");
        cmd.append("    OPTIONAL { ").append(Const.URI_ORG).append(" ehr:hasEmail ?em } . \n");
        cmd.append("    OPTIONAL { ").append(Const.URI_ORG).append(" ehr:hasPhoneNumber ?pn } . \n");
        cmd.append("    OPTIONAL { ").append(Const.URI_ORG).append(" ehr:isDepartmentOf ?dp } . \n");
        cmd.append("    OPTIONAL { ").append(Const.URI_ORG).append(" ehr:isSubsidiaryOf ?sb } . \n");
        cmd.append("   }\n}");
        SparqlBuilder.replaceAll(cmd, "@id", String.valueOf(id));
        Organization org = null;

        try {
            logger.debug("### Starting to find an Org by given ID ###");
            ResultSet rs = conn.executeQuery(cmd.toString());
            if (rs.hasNext()) {
                org = one(rs.nextSolution());
            }
        } catch (Exception e) {
            logger.error("findById(), failed operation.", e);
            e.printStackTrace();
        } finally {
            conn.close();
        }

        return org;
    }

    public Organization findOrgRoot(String id) {
        Organization org = findById(Integer.parseInt(id));

        String cmd = "SELECT DISTINCT * WHERE {\n  " +
            "{ ?org owl:sameAs <http://axessinfo.com/resources/organizations/" +
            id + "> . }\n  " +
            "SERVICE <http://dbpedia.org/sparql> {\n  " +
            "OPTIONAL { ?org dbpo:country ?ctru . " +
            "?ctru rdfs:label ?ctr FILTER (lang(?ctr)=\"en\") } . \n  " +
            "OPTIONAL { {?org dbpo:city ?ctu} UNION {?org dbpo:locationCountry ?ctu} . " +
            "?ctu rdfs:label ?ct FILTER (lang(?ct)=\"en\") } . \n  " +
            "OPTIONAL { ?org dbpo:abstract ?ab FILTER ( lang(?ab) = \"en\" ) } . \n  " +
            "OPTIONAL { ?org dbpo:foundingDate ?fdt }\n }  " +
            "\n}";

        QueryExecution qe = null;
        try {
            qe = inf.create(cmd);
            ResultSet rs = qe.execSelect();
            //ResultSetFormatter.out(rs);
            if (rs.hasNext()) {
                setOrg(org, rs.nextSolution());
            }
            logger.debug(org.toString());
        } catch (Exception e) {
            String s = "Sorry, cannot get linked resources. Please try later!";
            logger.error(s + " " + e);
            e.printStackTrace();
            org.setComment(s + " " + e.getMessage());
        } finally {
            if (qe != null) {
                qe.close();
            }
        }

        return org;
    }

    private void setOrg(Organization org, QuerySolution qs) throws Exception {
        String en = "@en";
        Literal ct = qs.getLiteral("?ct");
        org.setCity(ct != null ? ct.getString().replace(en, "") : org.getCity());
        Literal ctr = qs.getLiteral("?ctr");
        org.setCountry(ctr != null ? ctr.getString().replace(en, "") : org.getCountry());
        Literal cmt = qs.getLiteral("?ab");
        org.setComment(cmt != null ? cmt.getString().replace(en, "") : org.getComment());
        Literal fdt = qs.getLiteral("?fdt");
        org.setFoundingDate(DateUtil.parse(fdt));
    }

    /**
     * Delete triples of an Organization by given ID.
     *
     * @param id An identifier of Organization.
     * @return boolean Indicates the operation is successful or not.
     */
    public boolean delete(int id) {
        StringBuffer cmd = new StringBuffer(512);
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("WITH ").append(Const.GRAPH_ORG + "\n");
        cmd.append("DELETE { ?o ?p ?v }\n");
        cmd.append("WHERE { \n");
        cmd.append("    ?o ?p ?v ; ehr:hasId ").append(id).append("\n}");

        return conn.execUpdate(cmd.toString(), inf);
    }

    public boolean signin(String email, String psw) throws Exception {
        StringBuffer cmd = new StringBuffer(256);
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("ASK { GRAPH " + Const.GRAPH_ORG +
                " { ?o a ehr:Organization . ?o ehr:hasEmail \"")
                .append(email).append("\" . ?o ehr:password \"")
                .append(psw).append("\" } }");
        return conn.executeAsk(cmd.toString());
    }

    public Organization findByEmailPsw(String email, String psw) {
        StringBuffer cmd = new StringBuffer(256);
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("SELECT ?o ?id ?nm ?er ?pn ?em \n");
        cmd.append("FROM ").append(Const.GRAPH_ORG).append("\n");
        cmd.append("WHERE{ \n");
        cmd.append("   GRAPH ").append(Const.GRAPH_ORG).append(" {\n");
        cmd.append("    ?o a ehr:Organization .\n");
        cmd.append("    ?o ehr:hasId ?id . \n");
        cmd.append("    ?o ehr:hasName ?nm . \n");
        cmd.append("    ?o ehr:hasEmail ?em . \n");
        cmd.append("    OPTIONAL { ?o ehr:hasEmployer ?er } . \n");
        cmd.append("    OPTIONAL { ?o ehr:hasPhoneNumber ?pn } . \n");
        cmd.append("    ?o ehr:hasEmail \"").append(email).append("\" . \n");
        cmd.append("    ?o ehr:password \"").append(psw).append("\" . \n");
        cmd.append("   }\n}");

        Organization org = null;
        try {
            ResultSet rs = conn.executeQuery(cmd.toString());
            if (rs.hasNext()) {
                org = one(rs.nextSolution());
            }
        } catch (Exception e) {
            logger.error("findByEmailPsw(), failed operation.", e);
            e.printStackTrace();
        } finally {
            conn.close();
        }
        return org;
    }

    public boolean batchSave(List<Organization> orgs) throws Exception {
        boolean isSuccess = true;
        StringBuffer cmd = new StringBuffer();
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        cmd.append("INSERT DATA {\n");
        cmd.append("  GRAPH ").append(Const.GRAPH_ORG).append(" { \n");

        for (Organization o: orgs) {
            if (o.getId() !=0 ) {
                throw new IllegalArgumentException("make sure id==0 while execute batch insert.");
            }
            if (StringUtil.isEmptyOrNull(o.getParent())) {
                throw new NullPointerException("Parent ID cannot be blank");
            }

            if (o.getOrgType().equals("Department")) {
                cmd.append("    " + Const.URI_ORG + " rdf:type ehr:Department ; \n");
                cmd.append("    ehr:isDepartmentOf ").append(SparqlBuilder.wrapOrg(o.getParent())).append(" ; \n");
            } else if (o.getOrgType().equals("Subsidiary")) {
                cmd.append("    " + Const.URI_ORG + " rdf:type ehr:Subsidiary ; \n");
                cmd.append("    ehr:isSubsidiaryOf ").append(SparqlBuilder.wrapOrg(o.getParent())).append(" ; \n");
            } else if (o.getOrgType().equals("Headquarters")) {
                cmd.append("    " + Const.URI_ORG + " rdf:type ehr:Headquarters ; \n");
                cmd.append("    ehr:isHeadquartersOf ").append(SparqlBuilder.wrapOrg(o.getParent())).append(" ; \n");
            } else {
                // ehr:Organization isn't supported by batch saving, so if not
                // above three types of Organization then throw an exception.
                throw new NullPointerException("Unsupported organization type: " + o.getOrgType());
            }

            cmd.append("    ehr:hasId @id ; \n");
            cmd.append("    ehr:hasName \"").append(o.getName()).append("\" ; \n");
            if (o.getEmail() != null) {
                cmd.append("    ehr:hasEmail \"").append(o.getEmail()).append("\" ; \n");
            }
            if (o.getEmployer() != null) {
                cmd.append("    ehr:hasEmployer ").append(o.getEmployer()).append(" ; \n");
            }
            if (o.getPassword() != null) {
                cmd.append("    ehr:password \"").append(o.getPassword()).append("\" ; \n");
            }
            if (o.getPhoneNumber() != null) {
                cmd.append("    ehr:hasPhoneNumber  \"").append(o.getPhoneNumber()).append("\" ; \n");
            }

            int id = IdGenerator.getInstance().getMaxOrgId();
            SparqlBuilder.replace(cmd, "@id", String.valueOf(++id));
            SparqlBuilder.replace(cmd, "@id", String.valueOf(id));
            IdGenerator.getInstance().setMaxOrgId(id);

            SparqlBuilder.replaceLast(cmd, ";", ".");
        }


        SparqlBuilder.replaceLast(cmd, "", "");
        cmd.append("  }\n}");

        return conn.execUpdate(cmd.toString(), inf);
    }

    public boolean batchUpdate(List<Organization> orgs) {
        boolean change = false;
        StringBuffer cmd = new StringBuffer();
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);

        for (Organization o: orgs) {

            // if fields are empty, the sparql will be still generated.
            // This segment will avoid the case.
            if (StringUtil.isEmptyOrNull(o.getName(), o.getEmail(),
                    o.getPhoneNumber(), o.getOrgType(), o.getParent())) {
                continue;
            }

            // get old one from DB by org ID.
            Organization old = findById(o.getId());

            cmd.append("WITH ").append(Const.GRAPH_ORG + "\n");
            cmd.append("DELETE { @nm @em @pn @ot @prt } \n");
            cmd.append("INSERT { @nm @em @pn @ot @prt} \n");
            cmd.append("WHERE {\n");
            cmd.append("  ?o a ehr:").append(old.getOrgType()).append(" .\n");
            cmd.append("  ?o ehr:hasId @id");
            cmd.append("\n};\n");

            prepareUpdateSparql(old, o, cmd);
            change = true;
        }

        boolean isSuccess = true;
        if (!change) return isSuccess;

        return conn.execUpdate(cmd.toString(), inf);
    }

    private void prepareUpdateSparql(Organization old, Organization o, StringBuffer cmd) {
        // check if the given organization object has no id value then return.
        if (o.getId() <= 0) {
            throw new NullPointerException("Cannot found Org ID for update");
        } else {
            int p = cmd.indexOf("@id");
            cmd.replace(p, p+3, String.valueOf(o.getId()));
        }

        // compare with old one and prepare partial SPARQL
        boolean seg = false;
        seg = SparqlBuilder.replace(cmd, "@nm", "hasName", old.getName(),
                o.getName(), seg,
                SparqlBuilder.isModified(o.getName(), old.getName()));
        seg = SparqlBuilder.replace(cmd, "@em", "hasEmail", old.getEmail(),
                o.getEmail(), seg,
                SparqlBuilder.isModified(o.getEmail(), old.getEmail()));
        seg = SparqlBuilder.replace(cmd, "@pn", "hasPhoneNumber", old.getPhoneNumber(),
                o.getPhoneNumber(), seg,
                SparqlBuilder.isModified(o.getPhoneNumber(), old.getPhoneNumber()));
        seg = SparqlBuilder.replace(cmd, "@ot", "rdf:type", old.getOrgType(), o.getOrgType(),
                seg, SparqlBuilder.isModified(o.getOrgType(), old.getOrgType()));
        SparqlBuilder.replace(cmd, "@prt", "is"+o.getOrgType()+"Of", old.getParent(), o.getParent(),
                seg, SparqlBuilder.isModified(o.getParent(), old.getParent()));
    }

    public boolean batchRemove(String[] ids) {
        StringBuffer cmd = new StringBuffer();
        ConnectDao conn = new ConnectDao();
        conn.setDefaultNS(cmd);
        for (String id: ids) {
            int i = Integer.parseInt(id);
            if (i <= 0) {
                throw new NullPointerException("Cannot found Org ID for re");
            }
            cmd.append("WITH ").append(Const.GRAPH_ORG + "\n");
            cmd.append("DELETE { ?o ?p ?v }\n");
            cmd.append("WHERE { \n");
            cmd.append("    ?o ?p ?v ; ehr:hasId ").append(i);
            cmd.append("\n};\n");
        }
        return conn.execUpdate(cmd.toString(), inf);
    }

}

